package database;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DBInterface {
     
     private PreparedStatement preparedStatement = null;
     private ResultSet rs = null;
     private String url = "jdbc:mysql://localhost/shoppinglist";
     private String user = "user";
     private String pass = "password";


	
	  private Connection conn;
	
	

	  public static void main(String[] args)
	  {
	    new DBInterface();
	  }
	 
	  public DBInterface()
	  {
		    try
		    {
			      Class.forName("com.mysql.jdbc.Driver").newInstance();
			      String url = "jdbc:mysql://localhost/NationalParksDatabase";
			      conn = DriverManager.getConnection(url, "username", "password");
			      conn.close();
		    }
		    catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());}
		    catch (IllegalAccessException ex) {System.err.println(ex.getMessage());}
		    catch (InstantiationException ex) {System.err.println(ex.getMessage());}
		    catch (SQLException ex)           {System.err.println(ex.getMessage());}
	  }
	  
	  public List<InventoryItem> getInventoryList() throws Exception{
          List<InventoryItem> inventoryitems = new ArrayList<InventoryItem>();
          try
          {
                  conn = DriverManager.getConnection(url, user, pass);
                  preparedStatement = conn.prepareStatement("SELECT * FROM trail_inventory");
                  rs = preparedStatement.executeQuery();
                  while(rs.next()){
                	  InventoryItem item = new InventoryItem(rs.getString("trail_name"), rs.getString("park_name"),
                			  				rs.getString("vista_point"), rs.getInt("slope"), rs.getInt("length"), 
                			  				rs.getString("terrain"), rs.getInt("difficulty"));
                	  inventoryitems.add(item);
                  }
          }
          catch (SQLException ex)
          {
                 Logger lgr = Logger.getLogger(DBInterface.class.getName());
                 lgr.log(Level.SEVERE, ex.getMessage(), ex);
          }
          finally
          {
                  try
                  {
			          if (preparedStatement != null)
			          {
			              preparedStatement.close();
			          }
			          if (conn != null)
			          {
			              conn.close();
			          }
                  }
                  catch (SQLException ex)
                  {
			          Logger lgr = Logger.getLogger(DBInterface.class.getName());
			          lgr.log(Level.SEVERE, ex.getMessage(), ex);
                  }
          }
          conn.close();
          return inventoryitems;
	  }
	  
	  public List<MaintenanceItem> getMaintanceList() throws Exception{
          List<MaintenanceItem> maintanceitems = new ArrayList<MaintenanceItem>();
          try
          {
                  conn = DriverManager.getConnection(url, user, pass);
                  preparedStatement = conn.prepareStatement("SELECT * FROM maint_user");
                  rs = preparedStatement.executeQuery();
                  while(rs.next()){
                	  MaintenanceItem item = new MaintenanceItem(rs.getString("trail_name"), 
                			  				 rs.getInt("melt_out"), rs.getInt("wash_out"), rs.getString("open"));
                	  maintanceitems.add(item);
                  }
          }
          catch (SQLException ex)
          {
                 Logger lgr = Logger.getLogger(DBInterface.class.getName());
                 lgr.log(Level.SEVERE, ex.getMessage(), ex);
          }
          finally
          {
                  try
                  {
			          if (preparedStatement != null)
			          {
			              preparedStatement.close();
			          }
			          if (conn != null)
			          {
			              conn.close();
			          }
                  }
                  catch (SQLException ex)
                  {
			          Logger lgr = Logger.getLogger(DBInterface.class.getName());
			          lgr.log(Level.SEVERE, ex.getMessage(), ex);
                  }
          }
          conn.close();
          return maintanceitems;
	  }
	  
	  
	  public void addToInventoryTable(String trail_name, String park_name,
										String vista_point, int slope, int length, 
										String terrain, int difficulty) throws Exception
      {
              try
              {
                      conn = DriverManager.getConnection(url, user, pass);
                      preparedStatement = conn.prepareStatement("INSERT INTO trail_inventory VALUES(?, ?, ?, ?, ?, ?, ?)");
                      preparedStatement.setString(1, trail_name);
                      preparedStatement.setString(2, park_name);
                      preparedStatement.setString(3, vista_point);
                      preparedStatement.setInt(4, slope);
                      preparedStatement.setInt(5, length);
                      preparedStatement.setString(6, terrain);
                      preparedStatement.setInt(7, difficulty);
                      preparedStatement.executeUpdate();
              }
              catch (SQLException ex)
              {
            	  Logger lgr = Logger.getLogger(DBInterface.class.getName());
            	  lgr.log(Level.SEVERE, ex.getMessage(), ex);
              }
              finally
              {
                      try
                      {
			              if (preparedStatement != null)
			              {
			                  preparedStatement.close();
			              }
			              if (conn != null)
			              {
			                  conn.close();
			              }
                      }
                      catch (SQLException ex)
                      {
			              Logger lgr = Logger.getLogger(DBInterface.class.getName());
			              lgr.log(Level.SEVERE, ex.getMessage(), ex);
                      }
              }
              conn.close();
      }
	  
	  public void addToMaintenanceTable(String trail_name, int melt_out, int wash_out, 
										String open) throws Exception
	  {
		  try
		  {
				conn = DriverManager.getConnection(url, user, pass);
				preparedStatement = conn.prepareStatement("INSERT INTO trail_inventory VALUES(?, ?, ?, ?)");
				preparedStatement.setString(1, trail_name);
				preparedStatement.setInt(2, melt_out);
				preparedStatement.setInt(3, wash_out);
				preparedStatement.setString(4, open);
				preparedStatement.executeUpdate();
		  }
		  catch (SQLException ex)
		  {
				Logger lgr = Logger.getLogger(DBInterface.class.getName());
				lgr.log(Level.SEVERE, ex.getMessage(), ex);
		  }
		  finally
		  {
			  try
			  {
					if (preparedStatement != null)
					{
					    preparedStatement.close();
					}
					if (conn != null)
					{
					    conn.close();
					}
			  }
			  catch (SQLException ex)
			  {
					Logger lgr = Logger.getLogger(DBInterface.class.getName());
					lgr.log(Level.SEVERE, ex.getMessage(), ex);
			  }
		  }
		  conn.close();
	  }
	  
	  public  void updateInventoryTable (String trail_name, String park_name,
												String vista_point, int slope, int length, 
												String terrain, int difficulty) throws Exception 

		{
		    try
		    {
		    	conn = DriverManager.getConnection(url, user, pass);
		    	// create our java preparedstatement using a sql update query
		    	preparedStatement  = conn.prepareStatement(
				"UPDATE trail_inventory SET vista_point = ? AND slope = ? AND length = ? AND terrain = ? AND difficulty = ? WHERE trail_name = ?, park_name = ?");
				
				// set the preparedstatement parameters
                preparedStatement.setString(1, trail_name);
                preparedStatement.setString(2, park_name);
                preparedStatement.setString(3, vista_point);
                preparedStatement.setInt(4, slope);
                preparedStatement.setInt(5, length);
                preparedStatement.setString(6, terrain);
                preparedStatement.setInt(7, difficulty);
                preparedStatement.executeUpdate();
				// call executeUpdate to execute our sql update statement
                preparedStatement.executeUpdate();
                preparedStatement.close();
		    }
		    catch (SQLException se)
		    {
		    	// log the exception
		    	throw se;
		    }
		}
	  
	  public  void updateMaintenanceTable (String trail_name, int melt_out, int wash_out, 
										String open) throws Exception
	  {
			try
			{
				conn = DriverManager.getConnection(url, user, pass);
				// create our java preparedstatement using a sql update query
				preparedStatement  = conn.prepareStatement(
				"UPDATE trail_inventory SET melt_out = ? AND wash_out = ? AND open = ? WHERE trail_name = ?");
				
				// set the preparedstatement parameters
				preparedStatement.setString(1, trail_name);
				preparedStatement.setInt(2, melt_out);
				preparedStatement.setInt(3, wash_out);
				preparedStatement.setString(4, open);
			
				preparedStatement.executeUpdate();
				// call executeUpdate to execute our sql update statement
				preparedStatement.executeUpdate();
				preparedStatement.close();
			}
			catch (SQLException se)
			{
			// log the exception
				throw se;
			}
}
	  
	  public void deleteTrail(String name)
      {
              
              if(name.contains("'")){
                      name = name.replace("'", "\\'");
              }
              try
              {                 
                      conn = DriverManager.getConnection(url, user, pass);
                      preparedStatement = conn.prepareStatement("DELETE FROM trail_inventory WHERE trail_name = " + "'" + name + "'");
                      preparedStatement.executeUpdate();
                      preparedStatement = conn.prepareStatement("DELETE FROM maint_user WHERE trail_name = " + "'" + name + "'");
                      preparedStatement.executeUpdate();
                   
                      conn.close();
              }
              catch (SQLException ex)
              {
			          Logger lgr = Logger.getLogger(DBInterface.class.getName());
			          lgr.log(Level.SEVERE, ex.getMessage(), ex);
              }
             
      	}




	  
}